﻿IF (EXISTS(SELECT id from sysobjects where id=object_id('usp_searchCandidates')))
	DROP PROCEDURE [dbo].[usp_searchCandidates]
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create procedure usp_searchCandidates
@DistrictId as nvarchar(50),
@PanchayatId as nvarchar(50),
@WardId as nvarchar(50),
@Name as nvarchar(50),
@ElectionId as nvarchar(50),
@VoterId as nvarchar(50),
@Party as nvarchar(50)

as



declare @SQLMainWHERE		AS NVARCHAR(MAX)
declare @SQLMainSELECT		AS NVARCHAR(MAX)

set @SQLMainSELECT = 'select * from TblCandidate c, TblVoter v where c.voterid=v.voterid and  1=1'
set @SQLMainWHERE = ''


select @SQLMainWHERE =@SQLMainWHERE +  ' and c.DistrictId=''' + @DistrictId  + '''' where @DistrictId  is not null
select @SQLMainWHERE = @SQLMainWHERE + ' and c.PanchayatId=''' + @PanchayatId  + '''' where @PanchayatId  is not null
select @SQLMainWHERE = @SQLMainWHERE + ' and c.WardId=''' + @WardId  + '''' where @WardId  is not null
select @SQLMainWHERE = @SQLMainWHERE + ' and v.Name like ''%' + @Name  + '%''' where @Name  is not null
select @SQLMainWHERE = @SQLMainWHERE + ' and ElectionId=''' + @ElectionId + '''' where @ElectionId is not null
select @SQLMainWHERE = @SQLMainWHERE + ' and c.VoterId=''' + @VoterId + '''' where @VoterId is not null
select @SQLMainWHERE = @SQLMainWHERE + ' and c.Party=''' + @Party + '''' where @Party is not null



declare @query as nvarchar(max)
set @query = @SQLMainSELECT + @SQLMainWHERE
print @query
EXEC (@query)

/*
Declare @DistrictId as nvarchar(50)
Declare  @PanchayatId as nvarchar(50)
Declare @WardId as nvarchar(50)
Declare @Name as nvarchar(50)
Declare @ElectionId as nvarchar(50)
Declare @VoterId as nvarchar(50)
Declare @Party as nvarchar(50)


set @DistrictId = null
set  @PanchayatId = null
set @WardId = null
set @Name = 'karuna'
set @ElectionId = null
set @VoterId = 'v00097'
set @Party = null

execute usp_searchCandidates  @DistrictId,@PanchayatId,@WardId ,@Name ,
@ElectionId,@VoterId,@Party

*/

